import pymysql
import pandas as pd
import xlrd

file = r'C:\Users\Administrator\Desktop\1.xls'

def totxt(str):
    file = 'C:\\Users\\Administrator\\Desktop\\1.txt'
    log = open(file, 'a', newline='')
    print('\r\n', file=log)
    print(str, file=log)
    print('\r\n', file=log)
    log.close()

def match(i,list,result):
    if len(result) == 1 :
        res = result[0]
        if res[0] == list[0] and res[1] == list[1] and res[2] == list[2] and res[3] == list[3]:
            pass
        else:
            totxt(i + 1)
            totxt("报名信息是：")
            totxt(list)
            totxt("数据库信息是：")
            totxt(res)
    else :
        totxt(i + 1)
        totxt("报名信息是：")
        totxt(list)
        totxt("数据库信息是：")
        totxt(result)

def match1():
    wb = xlrd.open_workbook(file)
    ws = wb.sheet_by_name("Sheet1")
    rows = ws.nrows
    for r in range(rows):
        list = ws.row_values(r)
        name = list[0]
        conn = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8')
        sql = "select 姓名,性别,单位及职务,身份 from 省总委员表 where 姓名 = '%s'" % name
        cursor = conn.cursor()
        cursor.execute(sql)
        res = cursor.fetchall()
        cursor.close()
        conn.close()
        match(r, list, res)

def remain():
    conn = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8')
    sql = "select * from 省总委员表 order by 所属ID ASC, 内部顺序 ASC"
    frame = pd.read_sql(sql, conn)

    wb = xlrd.open_workbook(file)
    ws = wb.sheet_by_name("Sheet1")
    rows = ws.nrows

    for r in range(rows):
        list = ws.row_values(r)
        name = list[0]
        frame = frame.drop(frame[frame.姓名 == name ].index)
    frame.to_excel(r'C:\Users\Administrator\Desktop\未匹配上人员名单1.xls')

match1()
remain()